import json
import re
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Any, Optional

import pytz
from openpyxl.worksheet.worksheet import Worksheet

from app.utils.exceptions import ResourceNotFoundError
from app.envConfig import ENV_CONFIG

BEIJING_TZ = pytz.timezone('Asia/Shanghai')


def remove_trailing_parentheses(name: str) -> str:
    if name.endswith(')'):
        last_open_paren = name.rfind('(')
        if last_open_paren > 0:
            return name[:last_open_paren].strip()
    return name


def week_field(week_num: int):
    return f"week{week_num:02d}"


def symbol_info(ws: Worksheet, row: int, col: int, symbol: str):
    if symbol == None:
        ws.cell(row=row, column=col, value="理论+实践教学")
    elif symbol == "△":
        ws.cell(row=row, column=col, value="机动")
    elif symbol == "★":
        ws.cell(row=row, column=col, value="军训")
    elif symbol == "-":
        ws.cell(row=row, column=col, value="毕业实习")
    elif symbol == "z":
        ws.cell(row=row, column=col, value="综合实践")
    elif symbol == "M":
        ws.cell(row=row, column=col, value="模拟考")
    elif symbol == "·":
        ws.cell(row=row, column=col, value="期中考试")
    elif symbol == "：":
        ws.cell(row=row, column=col, value="期末考试")
    elif symbol == "#1":
        ws.cell(row=row, column=col, value="专业实训（其中，公共基础课停,综合教育不停）")
    elif symbol == "#2":
        ws.cell(row=row, column=col, value="专业实训（其中，语数英不停，综合教育不停）")
    else:
        ws.cell(row=row, column=col, value=symbol)


def generate_week_serial_sql(weeks: int) -> str:
    origin_sql = "SELECT 1 AS n UNION SELECT 2"
    if weeks <= 2:
        return origin_sql
    else:
        if weeks <= 20:
            week_numbers = [f" UNION SELECT {i}" for i in range(3, weeks + 1)]
        else:
            week_numbers = [f" UNION SELECT {i}" for i in range(3, 21)]
        return origin_sql + "".join(week_numbers)


def str_to_bool(check: str):
    if check in ["false", "False", "0"]:
        return False
    else:
        return True


def get_file_extension(filename: str, with_dot: bool = False) -> str:
    """
    安全获取文件扩展名
    :param filename:
    :param with_dot:
    :return:
    """
    if not filename:
        return ""
    suffix = Path(filename).suffix.lower()
    if with_dot:
        return suffix
    else:
        return suffix[1:] if suffix else ""


def localize_naive_datetime(value):
    if isinstance(value, datetime) and value.tzinfo is None:
        return BEIJING_TZ.localize(value)
    return value


def filtered_data(records: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    converted_rows = []
    for row in records:
        row_dict = dict(row)
        row_dict['tgp_check'] = bool(row_dict.get('tgp_check'))
        row_dict['dept_check'] = bool(row_dict.get('dept_check'))
        row_dict['submit_time'] = localize_naive_datetime(row_dict['submit_time'])
        converted_rows.append(row_dict)
    return converted_rows


def from_list_orm_to_list_dict(list_orm: List[Any], rename_map: Dict) -> List[Dict[str, Any]]:
    data = []
    for record in list_orm:
        record_dict = {
            rename_map.get(column.name, column.name): getattr(record, column.name)
            for column in record.__table__.columns
        }
        data.append(record_dict)
    return data


def extract_week_number(filename: str) -> Optional[int]:
    file_name = filename.split('_')[-1]
    if not file_name:
        file_name = filename
    pattern1 = r'第(\d+)周'
    match1 = re.search(pattern1, file_name)
    if match1:
        return int(match1.group(1))
    return -1


def extract_filename(filename: str) -> Dict:
    # 允许的资料类型列表
    ALLOWED_MATERIAL_TYPES = [
        '教案',
        '教师教学工作情况考核表',
        '课程成绩报告单及教学质量分析表',
        '实训计划',
        '授课计划',
        '课程标准',
        '专业教学标准'
    ]

    if not (filename.endswith('.docx') or filename.endswith('.xlsx')):
        raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件格式异常，仅支持docx和xlsx格式"})

    # 去除文件扩展名
    name_without_ext = re.sub(r'\.(docx|xlsx)$', '', filename)
    # 匹配学期
    semester_pattern = r'^(.+?学期)'
    semester_match = re.match(semester_pattern, name_without_ext)
    if not semester_match:
        raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名格式异常：无法匹配学期信息"})
    semester = semester_match.group(1)
    remaining = name_without_ext[len(semester):]
    # 匹配资料类型（在"《"前面，学期后面的内容）
    material_type_pattern = r'^(.+?)《'
    material_type_match = re.match(material_type_pattern, remaining)
    if not material_type_match:
        raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名格式异常：无法匹配资料类型"})
    material_type = material_type_match.group(1)
    # 检查资料类型是否在允许列表中
    if material_type not in ENV_CONFIG.reverse_material_type_mapping.keys():
        raise ResourceNotFoundError(
            extra_details={"u-facingMsg": f"资料类型异常：'{material_type}'不是合法的资料类型"})
    remaining = remaining[len(material_type):]
    # 匹配课程名称（在《》内的内容）
    course_name_pattern = r'^《(.+?)》'
    course_name_match = re.match(course_name_pattern, remaining)
    if not course_name_match:
        raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名格式异常：无法匹配课程名称"})

    course_name = course_name_match.group(1)
    remaining = remaining[len(f"《{course_name}》"):]
    # 检查是否有下划线分隔符
    if not remaining.startswith('_'):
        raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名格式异常：缺少下划线分隔符"})

    remaining = remaining[1:]  # 移除第一个下划线

    # 分割剩余部分
    parts = remaining.split('_')

    # 根据资料类型处理不同的部分
    if material_type == "教案":
        # 教案类型：应该有3个部分 [班级, 教师姓名, 周数]
        if len(parts) < 3:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教案文件名格式异常：缺少必要的下划线分隔"})

        class_name = parts[0]
        teacher_name = parts[1]
        week_part = parts[2]
        # 提取周数
        week = extract_week_number(week_part)
    else:
        if len(parts) < 2:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "文件名格式异常：缺少必要的下划线分隔"})

        class_name = parts[0]
        teacher_name = parts[1]
        week = None

    # 构建返回结果
    result = {
        "semester": semester,
        "material_type": ENV_CONFIG.reverse_material_type_mapping.get(material_type),
        "course_name": course_name,
        "class_name": class_name,
        "teacher_name": teacher_name,
    }

    # 如果是教案，添加周数
    if week:
        result["week"] = week
    else:
        result["week"] = -1
    return result


# 辅助函数：根据文件扩展名获取content_type
def get_content_type(filename: str) -> str:
    """根据文件扩展名返回对应的content_type"""
    if filename.endswith('.docx'):
        return "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
    else:
        return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"


def extract_conclusion(report:str):
    if not report:
        return None
    pattern1 = r"总体结论：\s*(.+)"
    pattern2 = r"最终结论：\s*(.+)"
    match1 = re.search(pattern1, report)
    match2 = re.search(pattern2, report)
    if match1:
        return match1.group(1).strip()
    elif match2:
        return match2.group(1).strip()
    else:
        return None


    
def parse_ai_check_result(check_result):
    if not check_result:
        return None
    result = json.loads(check_result)
    result_str = ", ".join([f"{key}: {value}" for key, value in result.items()])
    return result_str

